﻿using Microsoft.VisualStudio.Modeling;
using System;
using System.Data;
using System.Text;

namespace Project.Co_DataTransformationPrototype
{
    public class AdditionMapping : MappingBase
    {
        private readonly string strTempColumnName = "PH__Temp_Colmn_ID";

        public AdditionMapping(EventContext eventContext)
        {
            this.eventContext = eventContext;
            //generateSQLCodeMapping();
        }

        internal override void generateSQLCodeMapping(StringBuilder existingScript = null)
        {
            strSQLMapping = new StringBuilder();
            
            if (existingScript != null)
                strSQLMapping.Append(existingScript);

            if (typeof(Entity).Equals(eventContext.elementEventArgs.ModelElement.GetType()))
                addTableSQLCode();
            else if (typeof(EntityAttribute).Equals(eventContext.elementEventArgs.ModelElement.GetType()))
                addColumnSQLCode();
        }

        private void addTableSQLCode()
        {
            Entity entity = eventContext.elementEventArgs.ModelElement as Entity;
            if (!(entity.IsImported))
            {
                strSQLMapping.AppendFormat(@"/*** --- Creating table for added entity '{0}' --- ***/" + Environment.NewLine, entity.Name);
                strSQLMapping.AppendFormat(@"CREATE TABLE [{0}].{1} (" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, entity.Name);
                strSQLMapping.AppendFormat("    {0} " + MappingTypeConvertion.translateDataTypeIntoSQLString(SqlDbType.NVarChar), strTempColumnName);
                foreach (EntityAttribute attribute in entity.EntityAttributes)
                {
                    strSQLMapping.Append(@"," + Environment.NewLine);
                    strSQLMapping.AppendFormat("    {0} {1}", attribute.Name, MappingTypeConvertion.translateDataTypeIntoSQLString(attribute.Type));
                }
                strSQLMapping.AppendLine(Environment.NewLine + ");" + Environment.NewLine + "GO" + Environment.NewLine);
            }
        }

        private void addColumnSQLCode()
        {
            EntityAttribute attribute = eventContext.elementEventArgs.ModelElement as EntityAttribute;
            
            // Actual SQL mapping for the creation of an attribute
            strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, attribute.Entity.Name);
            strSQLMapping.AppendFormat(@"    ADD {0} {1} NULL" + Environment.NewLine, attribute.Name, MappingTypeConvertion.translateDataTypeIntoSQLString(attribute.Type));
            strSQLMapping.AppendLine(@";"); //@"    DEFAULT NULL;");
            strSQLMapping.AppendLine("GO" + Environment.NewLine);

            // TODO: Find a way to use entity state to avoid this extra check !!!!
            strSQLMapping.AppendFormat(@"IF COL_LENGTH('{0}','{1}') IS NOT NULL" + Environment.NewLine, attribute.Entity.Name, strTempColumnName);
            strSQLMapping.AppendLine(@"BEGIN");
            strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, attribute.Entity.Name);
            strSQLMapping.AppendFormat(@"    DROP COLUMN {0}" + Environment.NewLine, strTempColumnName);
            strSQLMapping.AppendLine(@"END");
            strSQLMapping.AppendLine("GO" + Environment.NewLine);
        }

    }
}
